package com.aier.common.dao.impl;

import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.KeyHolder;

import com.aier.common.common.Constant;
import com.aier.common.dao.ICommonDAO;

public class CommonDAOImpl_J extends JdbcDaoSupport implements ICommonDAO {

    private boolean show_sql;
    private boolean show_error;

    @Override
    public int update(String sql) {
        if (show_sql) {
            System.out.println(sql);
        }
        return this.getJdbcTemplate().update(sql);
    }

    @Override
    public int update(String sql, Object[] args) {
        if (show_sql) {
            System.out.println(sql);
        }

        if (!show_error) {
            return this.getJdbcTemplate().update(sql, args);
        } else {
            try {
                return this.getJdbcTemplate().update(sql, args);
            } catch (Exception e) {
                e.printStackTrace();
                return 0;
            }
        }
    }

    @Override
    public int update(String sql, Object[] args, int[] types) {
        if (show_sql) {
            System.out.println(sql);
        }

        if (!show_error) {
            return this.getJdbcTemplate().update(sql, args, types);
        } else {
            try {
                return this.getJdbcTemplate().update(sql, args, types);
            } catch (Exception e) {
                e.printStackTrace();
                return 0;
            }
        }
    }

    @Override
    public List<?> list(String sql, Object[] args) {
        if (show_sql) {
            System.out.println(sql);
        }

        if (!show_error) {
            List<?> list = this.getJdbcTemplate().queryForList(sql, args);
            return list;
        } else {
            try {
                List<?> list = this.getJdbcTemplate().queryForList(sql, args);
                return list;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    @Override
    public List<?> list(String sql) {
        if (show_sql) {
            System.out.println(sql);
        }

        if (!show_error) {
            List<?> list = this.getJdbcTemplate().queryForList(sql);
            return list;
        } else {
            try {
                List<?> list = this.getJdbcTemplate().queryForList(sql);
                return list;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    @Override
    public List<?> list(String sql, List<Object> args, RowMapper<?> mapper) {
        if (null == args) {
            list(sql, new Object[0], mapper);
        }
        Object[] os = new Object[args.size()];
        for (int i = 0; i < os.length; i++) {
            os[i] = args.get(i);
        }
        return list(sql, os, mapper);
    }

    @Override
    public Object listByObject(String sql, List<Object> args, RowMapper<?> mapper) {
        if (null == args) {
            return listByObject(sql, new Object[0], mapper);
        }
        Object[] os = new Object[args.size()];
        for (int i = 0; i < os.length; i++) {
            os[i] = args.get(i);
        }
        return listByObject(sql, os, mapper);
    }

    @Override
    public Object listByObject(String sql, Object[] args, RowMapper<?> mapper) {

        if (!show_error) {
        	System.out.println(sql);
            try {
                if (show_sql) {
                    System.out.println(sql);
                }
                Object o = this.getJdbcTemplate().queryForObject(sql, args,
                        mapper);
                return o;
            } catch (EmptyResultDataAccessException e) {
                // 未查询到数据
                return null;
            }
        } else {
            try {
                if (show_sql) {
                    System.out.println(sql);
                }
                Object o = this.getJdbcTemplate().queryForObject(sql, args,
                        mapper);
                return o;
            } catch (EmptyResultDataAccessException e) {
                // 未查询到数据
                return null;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    @Override
    public Object listByObject(String sql, RowMapper<?> mapper) {
        Object o = this.getJdbcTemplate().queryForObject(sql, mapper);
        return o;
    }

    @Override
    public List<?> list(String sql, Object[] args, RowMapper<?> mapper) {
        if (show_sql) {
            System.out.println(sql);
        }

        if (!show_error) {
            List<?> list = this.getJdbcTemplate().query(sql, args,
                    mapper);
            return list;
        } else {
            try {
                List<?> list = this.getJdbcTemplate().query(sql,
                        args, mapper);
                return list;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    @Override
    public int insert(String table, Map<String, Object> par) {
        return insert(table, par, null);
    }

    @Override
    public int update(String table, Map<String, Object> par,
            Map<String, Object> args) {
        StringBuffer sql = new StringBuffer("update ").append(table).append(
                " set ");
        Object[] obj = null;
        if (null != args && args.size() > 0) {
            obj = new Object[par.keySet().size() + args.keySet().size()];
        } else {
            obj = new Object[par.keySet().size()];
        }
        int i = 0;
        Iterator<String> iter = par.keySet().iterator();
        while (iter.hasNext()) {
            String key = iter.next();
            sql.append(key).append("=? ");
            obj[i++] = par.get(key);
            if (iter.hasNext()) {
                sql.append(",");
            }
        }
        if (null != args && args.size() > 0) {
            sql.append("where ");
            iter = args.keySet().iterator();
            while (iter.hasNext()) {
                String key = iter.next();
                sql.append(key).append("=? ");
                obj[i++] = args.get(key);
                if (iter.hasNext()) {
                    sql.append("and ");
                }
            }
        }
        return update(sql.toString(), obj);
    }

    @Override
    public List<?> list(String sql, RowMapper<?> mapper) {
        if (show_sql) {
            System.out.println(sql);
        }

        if (!show_error) {
            @SuppressWarnings({ "unchecked", "rawtypes" })
            List<?> list = (List<?>) this.getJdbcTemplate().query(sql,
                    new RowMapperResultSetExtractor(mapper));
            return list;
        } else {
            try {
                @SuppressWarnings({ "unchecked", "rawtypes" })
                List<?> list = (List<?>) this.getJdbcTemplate().query(sql,
                        new RowMapperResultSetExtractor(mapper));
                return list;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    @Override
    public int insert(String table, Map<String, Object> par,
            Map<String, Integer> type) {
        StringBuffer sql = new StringBuffer("insert into ").append(table)
                .append("(");
        StringBuffer sp = new StringBuffer();
        Object[] obj = new Object[par.keySet().size()];
        int i = 0;
        Iterator<String> iter = par.keySet().iterator();
        int[] types = new int[par.keySet().size()];
        while (iter.hasNext()) {
            String key = iter.next();
            sql.append(key);
            sp.append("?");
            obj[i] = par.get(key);
            if (null != type) {
                types[i] = type.get(key);
            }
            if (iter.hasNext()) {
                sql.append(",");
                sp.append(",");
            }
            i++;
        }
        sql.append(") value(").append(sp).append(")");

        return null != type ? update(sql.toString(), obj, types) : update(
                sql.toString(), obj);
    }

    @Override
    public int insert(PreparedStatementCreator pc) {

        if (!show_error) {
            return this.getJdbcTemplate().update(pc);
        } else {
            try {
                return this.getJdbcTemplate().update(pc);
            } catch (Exception e) {
                e.printStackTrace();
                return 0;
            }
        }
    }

    @Override
    public KeyHolder insert(PreparedStatementCreator pc, KeyHolder keyHolder) {

        if (!show_error) {
            this.getJdbcTemplate().update(pc, keyHolder);
            return keyHolder;
        } else {
            try {
                this.getJdbcTemplate().update(pc, keyHolder);
                return keyHolder;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    @Override
    public Map<String, Object> queryPage(String sql, int page, int pagesize,
            Object[] args, RowMapper<?> mapper) {
        return queryPage(countByQualification(sql, args), sql, page, pagesize,
                args, mapper);
    }
    
    @Override
    public Map<String, Object> queryPageNew(String sql, int page, int pagesize,
            Object[] args, RowMapper<?> mapper) {
        return queryPage(countByQualificationNew(sql, args), sql, page, pagesize,
                args, mapper);
    }
    
    @Override
    public Map<String, Object> queryPageThree(String sql, int page, int pagesize,
            Object[] args, RowMapper<?> mapper) {
        return queryPage(countByQualificationThree(sql, args), sql, page, pagesize,
                args, mapper);
    }
    
    public Map<String, Object> queryPageFour(String sql, int page, int pagesize,
            Object[] args, RowMapper<?> mapper) {
        return queryPage(countByQualificationFour(sql, args), sql, page, pagesize,
                args, mapper);
    }

    @Override
    public Map<String, Object> queryPage(String sql, int page, int pagesize,
            List<Object> args, RowMapper<?> mapper) {
        Object[] os = new Object[args.size()];
        for (int i = 0; i < os.length; i++) {
            os[i] = args.get(i);
        }
        return queryPage(countByQualification(sql, os), sql, page, pagesize,
                os, mapper);
    }

    
    @Override
    public Map<String, Object> queryPage(String sql, String countsql, int page,
            int pagesize, List<Object> args,List<Object> cargs, RowMapper<?> mapper) {
        Object[] os = new Object[args.size()];
        for (int i = 0; i < os.length; i++) {
            os[i] = args.get(i);
        }
        Object[] cos = new Object[cargs.size()];
        for (int i = 0; i < cos.length; i++) {
            cos[i] = cargs.get(i);
        }
        
        int count = 0;
        if (!show_error) {
            try {
                count = this.getJdbcTemplate().queryForInt(countsql, cos);
            } catch (IncorrectResultSizeDataAccessException e) {
                count = this.getJdbcTemplate().queryForList(countsql, cos)
                        .size();
            }
        } else {
            try {
                count = this.getJdbcTemplate().queryForInt(countsql, cos);
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
        return queryPage(count, sql, page, pagesize,
                os, mapper);
    }

    @Override
    public Map<String, Object> queryPage(String sql, String countsql, int page,
            int pagesize, Object[] args, Object[] cargs, RowMapper<?> mapper) {
        int count = 0;
        if (!show_error) {
            try {
                count = this.getJdbcTemplate().queryForInt(countsql, cargs);
            } catch (IncorrectResultSizeDataAccessException e) {
                count = this.getJdbcTemplate().queryForList(countsql, cargs)
                        .size();
            }
        } else {
            try {
                count = this.getJdbcTemplate().queryForInt(countsql, cargs);
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
        return queryPage(count, sql, page, pagesize, args, mapper);
    }

    private Map<String, Object> queryPage(int count, String sql, int page,
            int pagesize, Object[] args, RowMapper<?> mapper) {
        // select * from table limit start,pageNum
        int pagecount = 0;
        Map<String, Object> map = new HashMap<String, Object>();
        map.put(PAGE_SIZE, new Integer(pagesize));
        if (count <= 0) {
            map.put(COUNT, new Integer(0));
            map.put(OBJ_LIST, null);
            map.put(PAGE, new Integer(1));
            map.put(PAGE_COUNT, new Integer(1));
            return map;
        } else if (count < pagesize) {
            pagecount = 1;
            map.put(PAGE_COUNT, new Integer(1));
        } else if (count % pagesize == 0) {
            pagecount = count / pagesize;
            map.put(PAGE_COUNT, new Integer(pagecount));
        } else {
            pagecount = (count / pagesize) + 1;
            map.put(PAGE_COUNT, new Integer(pagecount));
        }
        map.put(COUNT, new Integer(count));
        if (page > pagecount) {
            page = pagecount;
        } else if (page <= 0) {
            page = 1;
        }
        map.put(PAGE, new Integer(page--));
        sql += " limit " + (page * pagesize) + ",";
        if ((page * pagesize) > count) {
            sql += count - (page * pagesize - count);
        } else {
            // sql += pagesize * (page + 1);
            sql += pagesize;
        }
        if (show_sql) {
            System.out.println(sql);
        }

        if (!show_error) {
            map.put(OBJ_LIST, this.getJdbcTemplate().query(sql, args, mapper));
            return map;
        } else {
            try {
                map.put(OBJ_LIST,
                        this.getJdbcTemplate().query(sql, args, mapper));
                return map;
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
    }

    private int countByQualification(String sql, Object[] args) {
        int count = 0;
        if (sql.split("union").length == 1) {
            return countByQual(sql,args);
        }
        String[] sqlArr = sql.split("union");
        int start=0;
        for (String sq :sqlArr) {
            int pcount = 0;
            for (int j =0;j<sq.length();j++) {
                if(sq.charAt(j) == '?') {
                    pcount++;
                }
            }
            
            Object[] argsl = new Object[pcount];
            for (int i=0;i<pcount;i++) {
                argsl[i] = args[start+i];
            }
            count += countByQual(sq,argsl);
            start += pcount;
        }
        
        return count;
    }
    
    private int countByQualificationNew(String sql, Object[] args) {
        String[] sqlArr = sql.split("union");
        String[] tmp1 = sqlArr[0].split("from");
        String sql1 = "select count(*) from " + tmp1[1] + " from "+tmp1[2];
        int count1 = 0;
        Object[] arg1 = new Object[2];
        arg1[0] = args[0];
        arg1[1] = args[1];
        if (show_sql) {
            System.out.println(sql1);
        }
        if (!show_error) {
            try {
                count1 = this.getJdbcTemplate().queryForInt(sql1, arg1);
            } catch (IncorrectResultSizeDataAccessException e) {
                count1 = this.getJdbcTemplate().queryForList(sql1, arg1).size();
            }
        } else {
            try {
                count1 = this.getJdbcTemplate().queryForInt(sql1, arg1);
            } catch (Exception e) {
                e.printStackTrace();
                count1 = 0;
            }
        }
        
        int count2 = 0;
        Object[] arg = new Object[2];
        arg[0] = args[0];
        arg[1] = args[1];
        String[] tmp = sqlArr[1].split("order by")[0].split("from");
        sql = "select count(u.uid) from " + tmp[Constant.num_5];
        for (int i = Constant.num_6; i < tmp.length; i++) {
            sql += "from" + tmp[i];
        }
        if (show_sql) {
            System.out.println(sql);
        }

        if (!show_error) {
            // !!!!!!!!!
            try {
                count2 = this.getJdbcTemplate().queryForInt(sql, arg);
            } catch (IncorrectResultSizeDataAccessException e) {
                count2 = this.getJdbcTemplate().queryForList(sql, arg).size();
            }
        } else {
            try {
                count2 = this.getJdbcTemplate().queryForInt(sql, arg);
            } catch (Exception e) {
                e.printStackTrace();
                count2 = 0;
            }
        }
        return count1+count2;
    }
    
    private int countByQual(String sql, Object[] args) {
        
        String[] tmp = sql.split("order by")[0].split("from");
        if (tmp[0].split("TIMESTAMP").length > 1) {
            sql = "select count(*) from " + tmp[1];
            for (int i = 2; i < tmp.length; i++) {
                sql += "from" + tmp[i];
            }
        } else {
            if (tmp[0].split("\\(").length > 1) {
                sql = "select count(*) from " + tmp[tmp.length - 1];
                /*
                 * for (int i = 2; i < tmp.length; i++) { sql += "from" + tmp[i]; }
                 */
            } else {
                sql = "select count(*) from " + tmp[1];
                for (int i = 2; i < tmp.length; i++) {
                    sql += "from" + tmp[i];
                }
            }
        }
        
        if (show_sql) {
            System.out.println(sql);
        }

        if (!show_error) {
            // !!!!!!!!!
            try {
                return this.getJdbcTemplate().queryForInt(sql, args);
            } catch (IncorrectResultSizeDataAccessException e) {
                return this.getJdbcTemplate().queryForList(sql, args).size();
            }
        } else {
            try {
                return this.getJdbcTemplate().queryForInt(sql, args);
            } catch (Exception e) {
                e.printStackTrace();
                return 0;
            }
        }
    }
    
    private int countByQualificationThree(String sql, Object[] args) {
        String[] tmp1 = sql.split("from");
        String sql1 = "select count(*) from " + tmp1[4];
        int count1 = 0;
        Object[] arg1 = new Object[2];
        arg1[0] = args[3];
        arg1[1] = args[4];
        if (show_sql) {
            System.out.println(sql1);
        }
        if (!show_error) {
            try {
                count1 = this.getJdbcTemplate().queryForInt(sql1, arg1);
            } catch (IncorrectResultSizeDataAccessException e) {
                count1 = this.getJdbcTemplate().queryForList(sql1, arg1).size();
            }
        } else {
            try {
                count1 = this.getJdbcTemplate().queryForInt(sql1, arg1);
            } catch (Exception e) {
                e.printStackTrace();
                count1 = 0;
            }
        }

        return count1;
    }
    
    private int countByQualificationFour(String sql, Object[] args) {
        String[] tmp1 = sql.split("from");
        String sql1 = "select count(*) from " + tmp1[4];
        int count1 = 0;
        Object[] arg1 = new Object[1];
        arg1[0] = args[3];
        if (show_sql) {
            System.out.println(sql1);
        }
        if (!show_error) {
            try {
                count1 = this.getJdbcTemplate().queryForInt(sql1, arg1);
            } catch (IncorrectResultSizeDataAccessException e) {
                count1 = this.getJdbcTemplate().queryForList(sql1, arg1).size();
            }
        } else {
            try {
                count1 = this.getJdbcTemplate().queryForInt(sql1, arg1);
            } catch (Exception e) {
                e.printStackTrace();
                count1 = 0;
            }
        }

        return count1;
    }
    

    public boolean isShow_error() {
        return show_error;
    }

    public void setShow_error(boolean show_error) {
        this.show_error = show_error;
    }

    public void setShow_sql(boolean show_sql) {
        this.show_sql = show_sql;
    }

    @Override
    public int insertSave(String table, Map<String, Object> par,
            List<String> upPar) {
        // INSERT INTO
        // user_fund(openID,fund,ticket,sfund_r,sfund_e,sticket_r,sticket_e,topup,fund_freeze)
        // VALUES (?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE
        // ticket=?,sticket_r=?,fund_freeze=?
        StringBuffer sql = new StringBuffer("insert into ").append(table)
                .append("(");
        Object[] obj = new Object[par.keySet().size() + upPar.size()];
        int i = 0;
        StringBuffer sp = new StringBuffer();
        Iterator<String> iter = par.keySet().iterator();
        // int[] types = new int[par.keySet().size()];
        while (iter.hasNext()) {
            String key = iter.next();
            sql.append(key);
            sp.append("?");
            obj[i] = par.get(key);
            /*
             * if (null != type) { types[i] = type.get(key); }
             */
            if (iter.hasNext()) {
                sql.append(",");
                sp.append(",");
            }
            i++;
        }

        sql.append(") value(").append(sp).append(") on duplicate key update ");

        for (int j = 0; j < upPar.size(); j++) {
            sql.append(upPar.get(j));
            obj[i++] = par.get(upPar.get(j));
            if (j < upPar.size() - 1) {
                sql.append("=?,");
            } else {
                sql.append("=?");
            }
        }

        if (!show_error) {
            return this.getJdbcTemplate().update(sql.toString(), obj);
        } else {
            try {
                return this.getJdbcTemplate().update(sql.toString(), obj);
            } catch (Exception e) {
                e.printStackTrace();
                return 0;
            }
        }
    }

    @Override
    public int countByQuali(String sql, Object[] args) {
        if (show_sql) {
            System.out.println(sql);
        }

        if (!show_error) {
            return countByQualification(sql, args);
            // !!!!!!!!!
            /*
             * try { return this.getJdbcTemplate().queryForInt(sql, args); }
             * catch (IncorrectResultSizeDataAccessException e) { return
             * this.getJdbcTemplate().queryForList(sql, args).size(); }
             */
        } else {
            try {
                return this.getJdbcTemplate().queryForInt(sql, args);
            } catch (Exception e) {
                e.printStackTrace();
                return 0;
            }
        }
    }

    @Override
    public JdbcTemplate getJDBCTemplate() {
        return this.getJdbcTemplate();
    }

}